﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace syc.Store
{
    public partial class 材料入出库管理 : System.Web.UI.Page
    {
        protected string Notice = "";
        string sql =
@"SELECT t1.ID
,t1.单据号
,t1.材料ID
,t2.品名
,t2.规格
,t2.单位
,case when t1.入出=1 or t1.入出=3 then
	case when t1.单位ID=1 then '一分厂'
	when t1.单位ID=2 then '二分厂'
	when t1.单位ID=3 then '三分厂'
	when t1.单位ID=4 then '四分厂'
	when t1.单位ID=5 then '五分厂'
	else
		'其他分厂'
	end
else
t3.名称
end as 供货或使用
,t1.日期
,t1.入出
,case when t1.入出=0 or t1.入出=2 then '入' else '出' end as 种类
,cast(t1.数量 as float) as 数量
,cast(t1.单价 as float) as 单价
,t1.金额
,cast(t1.结存数量 as float) as 结存数量
,cast(t1.结存金额 as float) as 结存金额
,t1.保管员
FROM 材料入出库 t1
left outer join 物料品种 t2 on t2.ID=t1.材料ID
left outer join 材料供货单位 t3 on t3.ID=t1.单位ID";

        decimal jc = 0, jcje = 0;
        decimal[] hj = { 0, 0, 0, 0 };

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!this.IsPostBack)
            {
                string s = Request.QueryString["fid"];
                if (s == null)
                {
                    s = "";
                }
                int fid = 0;
                int.TryParse(s, out fid);
                this.Label_FID.Text = fid.ToString("D7");

                this.SqlText.Text = sql;
                if (fid == 0)
                {
                }
                else if (fid % 100000 == 0)
                {
                    this.SqlText.Text += " where t1.材料ID>=" + (fid * 1000) + " and t1.材料ID<" + ((fid + 100000) * 1000);
                }
                else if (fid % 1000 == 0)
                {
                    this.SqlText.Text += " where t1.材料ID>=" + (fid * 1000) + " and t1.材料ID<" + ((fid + 1000) * 1000);
                }
                else
                {
                    this.SqlText.Text += " where t1.材料ID>=" + (fid * 1000) + " and t1.材料ID<" + ((fid + 1) * 1000);
                }
                this.SqlText.Text += " order by id desc";
                this.SqlDataSource1.SelectCommand = this.SqlText.Text;
            }
            else
            {
                this.SqlDataSource1.SelectCommand = this.SqlText.Text;
            }
        }

        protected void GridView1_DataBound(object sender, EventArgs e)
        {
            this.GridView1.SelectedIndex = -1;
            this.Button_Edit.Enabled = false;
            this.Button_Delete.Enabled = false;

            this.TextBox_Pager.Text = (this.GridView1.PageIndex + 1).ToString();
            this.Label_Pages.Text = " 共" + this.GridView1.PageCount.ToString() + "页 ";

            this.Button_Pager_First.CssClass = "pageturn-first-b";
            this.Button_Pager_First.Enabled = true;
            this.Button_Pager_Prev.CssClass = "pageturn-prev-b";
            this.Button_Pager_Prev.Enabled = true;
            this.Button_Pager_Next.CssClass = "pageturn-next-b";
            this.Button_Pager_Next.Enabled = true;
            this.Button_Pager_Last.CssClass = "pageturn-last-b";
            this.Button_Pager_Last.Enabled = true;
            if (this.GridView1.PageIndex == 0)
            {
                this.Button_Pager_Prev.CssClass = "pageturn-prev-g";
                this.Button_Pager_Prev.Enabled = false;
            }
            if (this.GridView1.PageIndex < 2)
            {
                this.Button_Pager_First.CssClass = "pageturn-first-g";
                this.Button_Pager_First.Enabled = false;
            }
            if (this.GridView1.PageIndex == this.GridView1.PageCount - 1 || this.GridView1.PageCount == 0)
            {
                this.Button_Pager_Next.CssClass = "pageturn-next-g";
                this.Button_Pager_Next.Enabled = false;

            }
            if (this.GridView1.PageIndex > this.GridView1.PageCount - 3)
            {
                this.Button_Pager_Last.CssClass = "pageturn-last-g";
                this.Button_Pager_Last.Enabled = false;
            }

        }
        protected void GridView1_PageIndexChanged(object sender, EventArgs e)
        {
            this.GridView1_DataBound(null, null);
        }
        protected void Pager_Click(object sender, EventArgs e)
        {
            if (sender.GetType().ToString() == "System.Web.UI.WebControls.Button")
            {
                switch (((Button)sender).CommandName)
                {
                    case "--":
                        this.GridView1.PageIndex = 0;
                        break;
                    case "-":
                        if (this.GridView1.PageIndex > 0) this.GridView1.PageIndex--;
                        break;
                    case "+":
                        if (this.GridView1.PageIndex < this.GridView1.PageCount - 1) this.GridView1.PageIndex++;
                        break;
                    case "++":
                        this.GridView1.PageIndex = this.GridView1.PageCount - 1;
                        break;
                }
            }
            else
            {

                int p;
                if (int.TryParse(TextBox_Pager.Text, out p))
                {
                    if (p >= 1 && p <= this.GridView1.PageCount)
                    {
                        this.GridView1.PageIndex = p - 1;
                    }
                }
                TextBox_Pager.Text = (this.GridView1.PageIndex + 1).ToString();
            }

        }
        protected void SqlDataSource1_Selected(object sender, SqlDataSourceStatusEventArgs e)
        {
            Label_Total.Text = "共查询到 " + e.AffectedRows + " 项记录";
        }
        protected void PageSizeOption_SelectedIndexChanged(object sender, EventArgs e)
        {
            this.GridView1.PageSize = int.Parse(this.PageSizeOption.Text);
        }
        protected void GridView1_SelectedIndexChanged(object sender, EventArgs e)
        {
            this.Button_Delete.CommandName = this.GridView1.SelectedDataKey[0].ToString();
            this.Button_Delete.Enabled = true;

            this.Button_Edit.Enabled = true;
            if ((byte)this.GridView1.SelectedDataKey[1] == 0)
            {
                this.Button_Edit.OnClientClick = "parent.swin2_load('/Store/材料入库单录入.aspx?id=" 
                    + this.Button_Delete.CommandName + "',710,410);return false;";
            }
            else if ((byte)this.GridView1.SelectedDataKey[1] == 1)
            {
                this.Button_Edit.OnClientClick = "parent.swin2_load('/Store/材料出库单录入.aspx?id="
                    + this.Button_Delete.CommandName + "',710,410);return false;";
            }
            else if ((byte)this.GridView1.SelectedDataKey[1] == 2)
            {
                this.Button_Edit.OnClientClick = "parent.swin2_load('/Store/材料入库红票.aspx?id="
                    + this.Button_Delete.CommandName + "',740,450);return false;";
            }
            else if ((byte)this.GridView1.SelectedDataKey[1] == 3)
            {
                this.Button_Edit.OnClientClick = "parent.swin2_load('/Store/材料出库红票.aspx?id="
                    + this.Button_Delete.CommandName + "',740,450);return false;";
            }
            else
            {
                this.Button_Edit.OnClientClick = "";
            }

        }
        private string GetState()
        {
            string state = "";
            int bm = 0;
            if (int.TryParse(this.BianMa.Text, out bm))
            {
                if (bm > 0)
                {
                    state += " and t1.材料ID=" + bm;
                }
            }
            int dh = 0;
            if (int.TryParse(this.DanHao.Text, out dh))
            {
                state += " and t1.单据号=" + dh;
            }

            DateTime d;
            if (DateTime.TryParse(this.Date1.Text, out d))
            {
                state += " and t1.日期>='" + d.ToShortDateString() + "'";
            }
            if (DateTime.TryParse(this.Date2.Text, out d))
            {
                state += " and t1.日期<='" + d.ToShortDateString() + "'";
            }
            if (this.GongHuo.Text != "0" && this.GongHuo.Text != "" && this.FenChang.Text!="0")
            {
                state += " and (t1.入出=0 and t1.单位ID=" + this.GongHuo.Text
                    + " or t1.入出=1 and t1.单位ID=" + this.FenChang.Text + ")";
            }
            else if (this.GongHuo.Text != "0" && this.GongHuo.Text != "" && this.FenChang.Text == "0")
            {
                state += " and (t1.入出=0 and t1.单位ID=" + this.GongHuo.Text
                    + " or t1.入出=1)";
            }
            else if (this.GongHuo.Text == "0" && this.FenChang.Text != "0")
            {
                state += " and (t1.入出=0 or t1.入出=1 and t1.单位ID=" + this.FenChang.Text + ")";
            }

            if (state.StartsWith(" and"))
            {
                state = state.Substring(4);
            }
            if (state != "")
            {
                state = " where " + state;
            }
            return state;
        }
        protected void Button_Query_Click(object sender, EventArgs e)
        {

            string state = this.GetState();

            this.SqlText.Text = sql + state + " order by t1.ID desc";
            this.SqlDataSource1.SelectCommand = "";
            this.SqlDataSource1.SelectCommand = this.SqlText.Text;
        }

        protected void Button_Reset_Click(object sender, EventArgs e)
        {

            this.BianMa.Text = "";
            this.PinMing.Text = "";
            this.GongHuo.Text = "0";
            this.FenChang.Text = "0";
            
            this.Date1.Text = "";
            this.Date2.Text = "";

        }

        protected void Button_Delete_Click(object sender, EventArgs e)
        {
            this.SqlDataSource1.DeleteParameters["ID"].DefaultValue = this.Button_Delete.CommandName;
            if (this.SqlDataSource1.Delete() > 0)
            {
                this.Notice = "·删除成功";
            }
            else
            {
                this.Notice = "·删除失败";
            }
        }

        protected void Button_Export_Click(object sender, EventArgs e)
        {
            int bm = 0;
            int.TryParse(this.BianMa.Text, out bm);
            if (bm <= 0)
            {
                this.Notice = "·请输入一种物料编码";
                return;
            }
            string part1 =
@"<html xmlns:o='urn:schemas-microsoft-com:office:office'
xmlns:x='urn:schemas-microsoft-com:office:excel'
xmlns='http://www.w3.org/TR/REC-html40'>
<head>
<meta http-equiv=Content-Type content='text/html; charset=utf-8'>
<style>
<!--table
@page
{mso-header-data:;
mso-footer-data:;
margin:.79in .31in .79in .31in;
mso-header-margin:.51in;
mso-footer-margin:.51in;
mso-page-orientation:landscape;
mso-horizontal-page-align:center;
}
-->
</style>
<!--[if gte mso 9]><xml>
 <x:ExcelWorkbook>
  <x:ExcelWorksheets>
   <x:ExcelWorksheet>
    <x:Name>材料明细账</x:Name>
    <x:WorksheetOptions>
     <x:DefaultRowHeight>285</x:DefaultRowHeight>
     <x:Print>
      <x:ValidPrinterInfo/>
      <x:Scale>100</x:Scale>
      <x:PaperSizeIndex>9</x:PaperSizeIndex>
      <x:HorizontalResolution>200</x:HorizontalResolution>
      <x:VerticalResolution>200</x:VerticalResolution>
     </x:Print>
     <x:Selected/>
     <x:Panes>
      <x:Pane>
       <x:Number>3</x:Number>
       <x:ActiveRow>0</x:ActiveRow>
       <x:ActiveCol>0</x:ActiveCol>
      </x:Pane>
     </x:Panes>
    </x:WorksheetOptions>
   </x:ExcelWorksheet>
 </x:ExcelWorkbook>
</xml><![endif]-->
</head>
<body>";
            string part2 =
@"<style>
tr{height:24px;}
td{text-align:center;font-size:10pt;}
td.s1{border:.5pt solid #000000;border-top:1pt solid #000000;border-left:1pt solid #000000;}
td.s2{border:.5pt solid #000000;border-top:1pt solid #000000;}
td.s3{border:.5pt solid #000000;border-top:1pt solid #000000;border-right:1pt solid #000000;}
td.s4{border:.5pt solid #000000;border-left:1pt solid #000000;}
td.s5{border:.5pt solid #000000;}
td.s6{border:.5pt solid #000000;border-right:1pt solid #000000;}
td.s7{border:.5pt solid #000000;border-bottom:1pt solid #000000;border-left:1pt solid #000000;}
td.s8{border:.5pt solid #000000;border-bottom:1pt solid #000000;}
td.s9{border:.5pt solid #000000;border-bottom:1pt solid #000000;border-right:1pt solid #000000;}
</style>
<table border=0 cellpadding=0 style=border-collapse:collapse>
<tr>
<td height=40 colspan=12 style=font-size:20pt;><b><u>&nbsp;材 料 明 细 账&nbsp;</u></b></td>
</tr>";
            string part3 =
@"<tr>
<td colspan=4 style=text-align:left;>材料名称：_</td>
<td colspan=3 style=text-align:left;>规格：_</td>
<td colspan=2 style=text-align:left;>计量单位：_</td>
<td colspan=3></td>
</tr>";
            string part4 =
@"<tr>
<td class=s1 width=70 rowspan=2>日期</td>
<td class=s2 colspan=2>记账凭据</td>
<td class=s2 width=120 rowspan=2>摘 要</td>
<td class=s2 width=90 rowspan=2>实际<br>单价(元)</td>
<td class=s2 colspan=2>收 入</td>
<td class=s2 colspan=2>支 出</td>
<td class=s2 colspan=2>结 存</td>
<td class=s3 width=80 rowspan=2>稽核人员<br>盖章</td>
</tr>
<tr>
<td class=s5 width=34>种类</td>
<td class=s5 width=60>号数</td>
<td class=s5 width=75>数 量</td>
<td class=s5 width=85>金 额(元)</td>
<td class=s5 width=75>数 量</td>
<td class=s5 width=85>金 额(元)</td>
<td class=s5 width=75>数 量</td>
<td class=s5 width=85>金 额(元)</td>
</tr>";
            string part5 =
@"<tr>
<td colspan=3></td>
<td style=text-align:right>记账员：</td>
<td colspan=4 style=text-align:left>　</td>
<td style=text-align:right>保管员：</td>
<td colspan=3 style=text-align:left>　</td>
</tr>
</table>
</body>
</html>";






            SqlConnection Conn = new SqlConnection();
            Conn.ConnectionString = ConfigurationManager.ConnectionStrings["SCMConnectionString1"].ConnectionString;
            Conn.Open();
            SqlCommand Cmd = new SqlCommand();
            Cmd.Connection = Conn;
            Cmd.CommandText = "select isnull(品名,'') as 品名,isnull(规格,'') as 规格,isnull(单位,'') as 单位 from 物料品种 where id=" + bm;
            SqlDataReader Reader = Cmd.ExecuteReader();
            if (!Reader.Read())
            {
                this.Notice = "没有该品种";
                Reader.Close();
                Conn.Close();
                Reader.Dispose();
                Cmd.Dispose();
                Conn.Dispose();
                return;
            }
            Response.Charset = "utf-8";
            Response.ContentEncoding = System.Text.Encoding.UTF8;
            Response.ContentType = "application/ms-excel";
            Response.AddHeader("content-disposition", "attachment; filename=\"" + Server.UrlEncode("材料明细账") + ".xls\"");

            part3 = part3.Replace("材料名称：_", "材料名称：" + (string)Reader["品名"]);
            part3 = part3.Replace("规格：_", "规格：" + (string)Reader["规格"]);
            part3 = part3.Replace("计量单位：_", "计量单位：" + (string)Reader["单位"]);

            Response.Write(part1);
            Response.Write(part2);
            Response.Write(part3);
            Response.Write(part4);

            Reader.Close();
            Cmd.CommandText = sql + this.GetState() + " order by t1.ID";

            Reader = Cmd.ExecuteReader();

            int n = 0;
            decimal sl = 0;
            decimal dj;
            while (Reader.Read())
            {
                jc = Convert.ToDecimal((double)Reader["结存数量"]);
                jcje = Convert.ToDecimal((double)Reader["结存金额"]);
                dj = Convert.ToDecimal((double)Reader["单价"]);

                //tmp = Reader["收入数量"];

                Response.Write("<tr><td class=s4>" + ((DateTime)Reader["日期"]).ToShortDateString() + "</td>");
                Response.Write("<td class=s5>" + (string)Reader["种类"] + "</td>");
                Response.Write("<td class=s5>" + (int)Reader["单据号"] + "</td>");
                if (Convert.IsDBNull(Reader["供货或使用"]))
                {
                    Response.Write("<td class=s5></td>"); 
                }
                else
                {
                    Response.Write("<td class=s5>" + (string)Reader["供货或使用"] + "</td>");
                }

                Response.Write("<td class=s5>" + dj + "</td>");
                if ((byte)Reader["入出"]==1 || (byte)Reader["入出"]==3 )
                {
                    Response.Write("<td class=s5></td>");
                    Response.Write("<td class=s5></td>");
                    sl =- Convert.ToDecimal((double)Reader["数量"]);
                    Response.Write("<td class=s5 style=text-align:right>" + (sl) + "</td>");
                    Response.Write("<td class=s5 style=text-align:right>" + (sl * dj).ToString() + "</td>");

                    hj[2] += sl;
                    hj[3] += (sl * dj);
                }
                else
                {
                    sl = Convert.ToDecimal((double)Reader["数量"]);
                    Response.Write("<td class=s5 style=text-align:right>" + sl + "</td>");
                    Response.Write("<td class=s5 style=text-align:right>" + (Convert.ToDecimal(sl) * dj).ToString() + "</td>");
                    Response.Write("<td class=s5></td>");
                    Response.Write("<td class=s5></td>");

                    hj[0] += sl;
                    hj[1] += (sl * dj);
                }
                Response.Write("<td class=s5 style=text-align:right>" + jc + "</td>");
                Response.Write("<td class=s5 style=text-align:right>" + jcje.ToString() + "</td>");
                Response.Write("<td class=s6></td></tr>");
                n++;
            }
            Response.Write("<tr><td class=s7 colspan=4></td><td class=s8>合计：</td>");
            Response.Write("<td class=s8 style=text-align:right>" + hj[0] + "</td>");
            Response.Write("<td class=s8 style=text-align:right>" + hj[1].ToString() + "</td>");
            Response.Write("<td class=s8 style=text-align:right>" + hj[2] + "</td>");
            Response.Write("<td class=s8 style=text-align:right>" + hj[3].ToString() + "</td>");
            Response.Write("<td class=s9 colspan=3></td></tr>");

            Reader.Close();
            Conn.Close();
            Reader.Dispose();
            Cmd.Dispose();
            Conn.Dispose();

            Response.Write(part5);
            Response.End();
        }

        protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
        {
            if (e.Row.RowType == DataControlRowType.DataRow)
            {
                if ((byte)DataBinder.Eval(e.Row.DataItem, "入出") == 1 || (byte)DataBinder.Eval(e.Row.DataItem, "入出") == 3)
                    e.Row.Cells[3].HorizontalAlign = HorizontalAlign.Right;
            }
        }

    }
}
